package com.example.fileprovider;
import android.util.Xml;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.xmlpull.v1.XmlPullParser;
import org.xmlpull.v1.XmlPullParserException;

import java.io.*;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipException;
import java.util.zip.ZipFile;
/**
 * project:
 * author: wzq
 * date: 2014/7/2
 * description:
 */
public class Xlsx2Html implements Htmlable {

    public Xlsx2Html(String filePath) {
        this.filePath = filePath;
        //
        //        int index1 = filePath.lastIndexOf("/");
        //        String picturePath = filePath.substring(0, index1);//文件所处文件夹
        //        String prefix = filePath.substring(index1 + 1, filePath.lastIndexOf("."));//文件名，不包含路径以及后缀名
        //        picMgr = new OfficePicMgr(picturePath, prefix);
    }


    private String filePath;//文件路径
    //private OfficePicMgr picMgr;
    private ByteArrayOutputStream output = new ByteArrayOutputStream();// 输出流
    @Override
    public InputStream getHtmlInputStream() {
        if(output == null) {
            return null;
        }

        byte[] bytes = output.toByteArray();
        try {
            output.write(bytes);
        } catch(IOException e) {
            e.printStackTrace();
        }
        return new ByteArrayInputStream(bytes);
    }


    /**
     * 取得单元格的值
     *
     * @param cell
     *
     * @return
     *
     * @throws java.io.IOException
     */
    private Object getCellValue(HSSFCell cell) throws IOException {
        Object value = "";
        if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            value = cell.getRichStringCellValue().toString();
        } else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            if(HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = (Date) cell.getDateCellValue();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                value = sdf.format(date);
            } else {
                double value_temp = (double) cell.getNumericCellValue();
                BigDecimal bd = new BigDecimal(value_temp);
                BigDecimal bd1 = bd.setScale(3, bd.ROUND_HALF_UP);
                value = bd1.doubleValue();
                DecimalFormat format = new DecimalFormat("#0.###");
                value = format.format(cell.getNumericCellValue());

            }
        }

        if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
            value = "";
        }
        return value;
    }

    String head = "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\"\"http://www.w3.org/TR/html4/loose.dtd\"><html><meta charset=\"utf-8\"><head></head><body>";// 定义头文件,我在这里加了utf-8,不然会出现乱码
    String tableBegin = "<table style=\"border-collapse:collapse\" border=1 bordercolor=\"black\">";
    String tableEnd = "</table>";
    String rowBegin = "<tr>";
    String rowEnd = "</tr>";
    String colBegin = "<td>";
    String colEnd = "</td>";
    String end = "</body></html>";
    @Override
    public boolean convertToHtml() {
        try {
            output.write(head.getBytes());
            output.write(tableBegin.getBytes());
            String str = "";
            String v = null;
            boolean flag = false;
            List<String> ls = new ArrayList<String>();
            try {
                ZipFile xlsxFile = new ZipFile(new File(filePath));// 地址
                ZipEntry sharedStringXML = xlsxFile.getEntry("xl/sharedStrings.xml");// 共享字符串
                InputStream inputStream = xlsxFile.getInputStream(sharedStringXML);// 输入流 目标上面的共享字符串
                XmlPullParser xmlParser = Xml.newPullParser();// new 解析器
                xmlParser.setInput(inputStream, "utf-8");// 设置解析器类型
                int evtType = xmlParser.getEventType();// 获取解析器的事件类型
                while(evtType != XmlPullParser.END_DOCUMENT) {// 如果不等于 文档结束
                    switch(evtType) {
                        case XmlPullParser.START_TAG: // 标签开始
                            String tag = xmlParser.getName();
                            if(tag.equalsIgnoreCase("t")) {
                                ls.add(xmlParser.nextText());
                            }
                            break;
                        case XmlPullParser.END_TAG: // 标签结束
                            break;
                        default:
                            break;
                    }
                    evtType = xmlParser.next();
                }
                ZipEntry sheetXML = xlsxFile.getEntry("xl/worksheets/sheet1.xml");
                InputStream inputStreamsheet = xlsxFile.getInputStream(sheetXML);
                XmlPullParser xmlParsersheet = Xml.newPullParser();
                xmlParsersheet.setInput(inputStreamsheet, "utf-8");
                int evtTypesheet = xmlParsersheet.getEventType();
                output.write(rowBegin.getBytes());
                int i = -1;
                while(evtTypesheet != XmlPullParser.END_DOCUMENT) {
                    switch(evtTypesheet) {
                        case XmlPullParser.START_TAG: // 标签开始
                            String tag = xmlParsersheet.getName();
                            if(tag.equalsIgnoreCase("row")) {
                            } else {
                                if(tag.equalsIgnoreCase("c")) {
                                    String t = xmlParsersheet.getAttributeValue(null, "t");
                                    if(t != null) {
                                        flag = true;
                                        System.out.println(flag + "有");
                                    } else {// 没有数据时 找了我n年,终于找到了 输入<td></td> 表示空格
                                        output.write(colBegin.getBytes());
                                        output.write(colEnd.getBytes());
                                        System.out.println(flag + "没有");
                                        flag = false;
                                    }
                                } else {
                                    if(tag.equalsIgnoreCase("v")) {
                                        v = xmlParsersheet.nextText();
                                        output.write(colBegin.getBytes());
                                        if(v != null) {
                                            if(flag) {
                                                str = ls.get(Integer.parseInt(v));
                                            } else {
                                                str = v;
                                            }
                                            output.write(str.getBytes());
                                            output.write(colEnd.getBytes());
                                        }
                                    }
                                }
                            }
                            break;
                        case XmlPullParser.END_TAG:
                            if(xmlParsersheet.getName().equalsIgnoreCase("row") && v != null) {
                                if(i == 1) {
                                    output.write(rowEnd.getBytes());
                                    output.write(rowBegin.getBytes());
                                    i = 1;
                                } else {
                                    output.write(rowBegin.getBytes());
                                }
                            }
                            break;
                    }
                    evtTypesheet = xmlParsersheet.next();
                }
                System.out.println(str);
            } catch(ZipException e) {
                e.printStackTrace();
            } catch(IOException e) {
                e.printStackTrace();
            } catch(XmlPullParserException e) {
                e.printStackTrace();
            }
            if(str == null) {
                str = "解析文件出现问题";
            }
            output.write(rowEnd.getBytes());
            output.write(tableEnd.getBytes());
            output.write(end.getBytes());
        } catch(Exception e) {
            System.out.println("read And Write Exception");
            return false;
        }
        return true;
    }
}
